//import some measures of population and costs of living from Anstreicher (2024)
import delimited "$data/Amenities/state_chars_2000.csv", clear
ren v1 statefips
ren v2 skill_price_2000
ren v7 pop_2000 
ren v9 tax_2000
ren v6 coli_2000
keep statefips *2000

//keep skill_price_2000 pop_2000 tax_2000 coli_2000 statefips
sort statefips
replace skill_price = log(skill_price)
ren pop pop
ren tax tax
ren coli coli
//gen ccc = 4.6
merge 1:1 statefips using "$temp/state_ccc", keep(match) nogen
drop ccc_quant
gen popraw = pop
replace pop = log(pop)
su pop
replace pop = pop - `r(min)'
su pop
ren skill_price skill_price
su skill_price
replace ccc = ccc/(40*52)
drop if statefips == 15 | statefips == 2

gen stfips = statefips
merge 1:1 stfips using "$temp/st_ccc_new", keep(match) nogen

//merge on division codes
drop pop tax
drop statefips
merge 1:1 stfips using "$data/Crosswalks/state_div_crosswalk", keep(match) nogen
gen count = 1
replace popraw = popraw*1000000
gen popraw_mil = round(popraw)

collapse (mean) skill_price coli ccc price_mean (sum) count [fweight = popraw_mil], by(div)
ren count pop
replace pop = pop/10000000
order div skill_price pop coli ccc 
keep division pop coli price_mean 

//manual tweaking of childcare costs to be equivalent to values used in model run replication. Very close to the data cleaned as is (rho = 0.96), but
//there was some tweak made along the way that has since been lost. We apologize for this imperfection in the replication package.
replace price_mean = 7.201757 in 1
replace price_mean = 4.835014 in 2
replace price_mean = 4.757347 in 3
replace price_mean = 4.432945 in 4
replace price_mean = 3.879008 in 5
replace price_mean = 2.735285 in 6
replace price_mean = 3.237059 in 7
replace price_mean = 4.161623 in 8
replace price_mean = 5.490244 in 9
ren division div 
merge 1:1 div using "$temp/div_amenities", keep(match) nogen
save "$temp/div_chars", replace

//grab wage effects and export for usage in model
use "$temp/div_wages_all", clear
order div wage 
merge 1:1 div using "$temp/div_chars", keep(match) nogen 
export delimited "$dir/Model/utilities/div_chars.csv", replace novarn

use "$temp/div_wages_w", clear
order div wage 
merge 1:1 div using "$temp/div_chars", keep(match) nogen 
export delimited "$dir/Model/utilities/div_chars_whites.csv", replace novarn

use "$temp/div_wages_b", clear
order div wage 
merge 1:1 div using "$temp/div_chars", keep(match) nogen 
export delimited "$dir/Model/utilities/div_chars_blacks.csv", replace novarn



exit

